﻿namespace MSSqlSchemaDoc.Provider.MicrosoftSqlServer
{
    using System.Collections.ObjectModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Text;
    using MSSqlSchemaDoc.Core;
    using MSSqlSchemaDoc.Core.DataStructures;
    using MSSqlSchemaDoc.Provider.MicrosoftSqlServer.Helpers;
    using MSSqlSchemaDoc.Provider.MicrosoftSqlServer.Resources;

    /// <summary>
    /// Contains the helper methods for table objects.
    /// </summary>
    public partial class SqlScriptingManager
    {
        /// <summary>
        /// Fetches a list of tables and extracts the information for each.
        /// </summary>
        /// <param name="connection">An open connection to the database.</param>
        /// <param name="objectType">The table object type.</param>
        private void ScriptTables(SqlConnection connection, DataStructureObjectType objectType)
        {
            // Load trigger data
            if (this.dmlTriggers == null)
            {
                this.ScriptTriggers(connection, DataStructureObjectType.DmlTrigger);
            }

            // Load table data
            StringBuilder tablesQuery = new StringBuilder(this.queryManager.Tables());
            this.InsertWhereClause(tablesQuery);
            SqlCommand c = new SqlCommand(tablesQuery.ToString(), connection);

            // Get index data for tables
            // Use a data table, since we need to evaluate it more than once
            StringBuilder indexQuery = new StringBuilder(this.queryManager.TableIndexes());
            this.InsertWhereClause(indexQuery);
            SqlDataAdapter adapter = new SqlDataAdapter(indexQuery.ToString(), connection);
            DataTable indexData = new DataTable();
            adapter.Fill(indexData);

            // Get foreign key data for tables
            // Use a data table, since we need to evaluate it more than once
            StringBuilder foreignKeyQuery = new StringBuilder(this.queryManager.ForeignKeys());
            this.InsertWhereClause(foreignKeyQuery);
            adapter = new SqlDataAdapter(foreignKeyQuery.ToString(), connection);
            DataTable foreignKeyData = new DataTable();
            adapter.Fill(foreignKeyData);
            
            using (IDataReader readerTables = c.ExecuteReader())
            {
                // First, check for data
                bool hasData = false;
                if (readerTables.Read())
                {
                    hasData = true;
                }

                while (hasData)
                {
                    string name = GetObjectName(readerTables);
                    this.OnBeginScriptingObject(name);

                    Table t = this.ConstructTable(readerTables, ref hasData, indexData, foreignKeyData);

                    this.OnCompleteScriptingObject(t, name);
                }
            }
        }

        /// <summary>
        /// Construct a table object.
        /// </summary>
        /// <param name="reader">Use the data in the data reader starting at it's current position.</param>
        /// <param name="hasMoreTables">This method will return false if this is the last table in the result set.</param>
        /// <param name="indexData">The index data for the tables.</param>
        /// <param name="foreignKeyData">The foreign key data for the tables.</param>
        /// <returns>The constructed table.</returns>
        private Table ConstructTable(IDataReader reader, ref bool hasMoreTables, DataTable indexData, DataTable foreignKeyData)
        {
            // Create the table object and set properties
            Table table = new Table()
            {
                Name = DataReaderHelper.GetString(reader, "ObjectName"),
                Schema = DataReaderHelper.GetString(reader, "SchemaName"),
                UsesAnsiNulls = DataReaderHelper.GetBool(reader, "UsesAnsiNulls")
            };

            table.ObjectId = DataReaderHelper.GetInt(reader, "ObjectId").Value;

            // Add the columns
            table.Columns = new Collection<Column>();
            int currentColumnId = DataReaderHelper.GetInt(reader, "ColumnId").Value;
            table.Columns.Add(ConstructColumn(new Column(), reader, indexData, foreignKeyData));

            // Get all the columns for the table by looping through the data reader
            string currentTableId = reader["ObjectId"].ToString();
            while ((hasMoreTables = reader.Read()) && currentTableId == reader["ObjectId"].ToString())
            {
                Column col;
                if (currentColumnId == DataReaderHelper.GetInt(reader, "ColumnId").Value)
                {
                    col = table.Columns[table.Columns.Count - 1];
                    ConstructColumn(col, reader, indexData, foreignKeyData);
                }
                else
                {
                    table.Columns.Add(ConstructColumn(new Column(), reader, indexData, foreignKeyData));
                }

                currentColumnId = DataReaderHelper.GetInt(reader, "ColumnId").Value;
            }

            // Load triggers
            table.Triggers = new Collection<TableTrigger>();
            foreach (DmlTrigger trigger in this.dmlTriggers)
            {
                if (table.ObjectId == trigger.ParentId)
                {
                    TableTrigger tt = new TableTrigger();

                    // Populate the data in the destination trigger
                    PropertyBinder.CopyValues(trigger, tt);
                    table.Triggers.Add(tt);
                }
            }

            // Indexes
            this.AddIndexes(table, indexData);

            // Foreign Keys
            AddForeignKeyContraintsForTable(table, foreignKeyData);

            // Documentation
            if (this.IncludeDocumentationObjects)
            {
                table.Documentation = this.ConstructTableDocumentation(table);
            }

            return table;
        }

        /// <summary>
        /// Constructs a table documentation object from a table object.
        /// </summary>
        /// <param name="table">The table to document.</param>
        /// <returns>The constructed documentation object.</returns>
        private TableDocumentation ConstructTableDocumentation(Table table)
        {
            TableDocumentation existingDocumentation;
            TableDocumentation documentation = this.ConstructDocumentationObject<TableDocumentation>(out existingDocumentation, table.Name, table.Schema);

            // Columns
            foreach (Column c in table.Columns)
            {
                ColumnDocumentation cd = new ColumnDocumentation() { Name = c.Name };
                cd.Documentation = GetDocumentationFromCollection<ColumnDocumentation>(existingDocumentation.Columns, cd.Name, cd.Schema);
                documentation.Columns.Add(cd);
            }

            // Table Triggers
            foreach (TableTrigger t in table.Triggers)
            {
                TableTriggerDocumentation d = new TableTriggerDocumentation() { Name = t.Name, Schema = t.Schema };
                d.Documentation = GetDocumentationFromCollection<TableTriggerDocumentation>(existingDocumentation.Triggers, d.Name, d.Schema);
                documentation.Triggers.Add(d);
            }

            // Indexes
            foreach (Index i in table.Indexes)
            {
                IndexDocumentation d = new IndexDocumentation() { Name = i.Name };
                d.Documentation = GetDocumentationFromCollection<IndexDocumentation>(existingDocumentation.Indexes, i.Name, string.Empty);
                documentation.Indexes.Add(d);
            }

            // Foreign Keys
            foreach (ForeignKey fk in table.ForeignKeys)
            {
                ForeignKeyDocumentation d = new ForeignKeyDocumentation() { Name = fk.Name };
                d.Documentation = GetDocumentationFromCollection<ForeignKeyDocumentation>(existingDocumentation.ForeignKeys, fk.Name, string.Empty);
                documentation.ForeignKeys.Add(d);
            }

            return documentation;
        }
    }
}
